module.exports = {
  ShowStatus: `SELECT
      a.configuration_id as Id,
      a.name,
      b.value,
      b.status,
      a.minimum,
      a.maximum,
      a.value_in_use,
      a.is_dynamic,
      a.is_advanced,
      a.description
    from sys.configurations a
    left join sys.sysconfigures b on a.configuration_id = b.config`,
  DataBases: `begin
      declare @db table(
        Name varchar(100),
        UsedSpace varchar(30),
        UnallocatedSpace varchar(30),
        Reserved varchar(30),
        DataSpace varchar(30),
        IndexSize varchar(30),
        UnUsedSpace varchar(30)
      );
    
      insert into @db exec sp_msforeachdb @command1="use [?]; execute sp_spaceused @oneresultset=1";
    
      select * from @db where Name not in('tempdb', 'model', 'msdb') order by Name;
    end`,
  Tables: `use #dbName; 
    begin
      declare @tableSpace table(
        name varchar(776),
        [rows] int,
        reserved varchar(50),  
        [data] varchar(50),
        index_size varchar(50),
        unused varchar(50)
      );

      insert into @tableSpace(name, [rows], reserved, [data], index_size, unused) exec sys.sp_MSforeachtable @command1="sp_spaceused '?'";
      
      WITH cte AS(
        SELECT
          SCHEMA_NAME(schema_id) AS [Schema],
          '['+SCHEMA_NAME(schema_id)+'].['+a.name+']' as table_name,
          type,
          object_id,
          a.name
        FROM sys.objects a
        WHERE type IN('U') AND a.name<>'sysdiagrams'
      )
      SELECT
        cte.[Schema],
        cte.table_name,
        p.HasIdentityColumn,
        CASE cte.type WHEN 'U' THEN 0 ELSE 1 END IsView,
        CASE WHEN a.value IS NULL THEN cte.name ELSE a.value END table_comment,
        b.reserved,
        b.[data] as used,
        b.[rows] as table_rows,
        b.[unused] as data_free
      FROM cte
      left join @tableSpace b on b.name=cte.table_name
      LEFT JOIN sys.extended_properties a ON cte.object_id=a.major_id AND a.minor_id=0 and a.name='MS_Description'
      OUTER APPLY(
        SELECT 1 AS HasIdentityColumn FROM sys.syscolumns s WHERE s.id=cte.object_id AND COLUMNPROPERTY(s.id, s.name, 'IsIdentity')=1
      ) p
      ORDER BY cte.[schema],cte.Name
    end`,
  TableColumns: `SELECT
      d.name as [tableName],
      a.name AS column_name
      ,a.name AS PropertyName
      ,case
        when b.name like '%char' and a.length>0 then b.name + '('+ cast(a.length/2 as varchar(10))+')'
        when b.name like '%char' and a.length<0 then b.name + '(max)'
        else b.name
      end AS column_type
      ,a.length AS character_length
      ,CASE WHEN EXISTS(
        SELECT 1 FROM #dbName.sys.sysobjects
        WHERE xtype= 'PK' and name in ( 
          SELECT
            name
          FROM #dbName.sys.sysindexes
          WHERE indid in(SELECT indid FROM #dbName.sys.sysindexkeys WHERE id = a.id AND colid=a.colid)
        )
      ) THEN 1 ELSE 0 END AS column_key
      ,a.IsNullable AS is_nullable
      ,COLUMNPROPERTY(a.id, a.name, 'IsIdentity') AS IsIdentity
      ,e.text AS column_default
      ,g.[value] AS column_comment
      ,a.colorder AS Sort
    FROM #dbName.sys.syscolumns a
    LEFT JOIN #dbName.sys.systypes b ON a.xusertype=b.xusertype
    INNER JOIN #dbName.sys.objects d ON a.id=d.object_id AND (d.type='U' OR d.type='V') AND d.name<>'dtproperties'
    INNER JOIN #dbName.sys.schemas s ON d.schema_id=s.schema_id
    LEFT JOIN #dbName.sys.syscomments e ON a.cdefault=e.id
    LEFT JOIN #dbName.sys.extended_properties g ON a.id=g.major_id AND a.colid=g.minor_id
    WHERE s.name=@schema AND d.name=@tableName
    ORDER BY a.id, a.colorder`,
  TablesIndexs: `select 
      b.[name] as table_name,
      a.[name] as index_name
    from #dbName.sys.indexes a 
    inner join #dbName.sys.tables b on a.object_id=b.object_id
    where b.name=@tableName`,
  TableDatas: `select * from #dbName.#tableName`,
  TableDatasCount: `select count(*) as totals from #dbName.#tableName`,
  TableDDL: `use #dbName;\r\nBEGIN
      DECLARE @isView INT;
      DECLARE @table NVARCHAR(50);
      DECLARE @schema NVARCHAR(50);
      DECLARE @tableDescription SQL_VARIANT;

      DECLARE @name NVARCHAR(50);
      DECLARE @dataType NVARCHAR(50);
      DECLARE @dataLength INT;
      DECLARE @isPrimaryKey INT;
      DECLARE @isNullable INT;
      DECLARE @isIdentity INT;
      DECLARE @dataDefault NVARCHAR(50);
      DECLARE @description SQL_VARIANT;

      DECLARE @index INT;
      DECLARE @ddl TABLE(id INT, source NVARCHAR(max));

      DECLARE cur_tables CURSOR FOR
        WITH cte AS(
          SELECT
            SCHEMA_NAME(schema_id) AS [Schema],
            type,
            name,
            object_id
          FROM #dbName.sys.objects
          WHERE type IN('U','V') AND Name<>'sysdiagrams'
        )
        SELECT
          cte.[Schema],
          cte.Name,
          CASE cte.type WHEN 'U' THEN 0 ELSE 1 END IsView,
          CASE WHEN a.value IS NULL THEN cte.name ELSE a.value END Comments
        FROM cte
        LEFT JOIN #dbName.sys.extended_properties a ON cte.object_id=a.major_id AND a.minor_id=0
        WHERE cte.name=@tableName
        ORDER BY cte.[schema], cte.Name;

      SET @index = 0;
      OPEN cur_tables;
      FETCH NEXT FROM cur_tables INTO @schema, @table, @isView, @tableDescription;

      WHILE @@FETCH_STATUS=0
      BEGIN
        DECLARE @sql NVARCHAR(max);
        DECLARE @columnDescription NVARCHAR(max);

        DECLARE cur_columns CURSOR FOR
          SELECT
            a.name AS Name
            ,UPPER(b.name) AS DataType
            ,a.length AS DataLength
            ,CASE WHEN EXISTS(
              SELECT 1 FROM #dbName.sys.sysobjects
              WHERE xtype= 'PK' and name in ( 
                SELECT
                  name
                FROM #dbName.sys.sysindexes
                WHERE indid in(
                  SELECT
                    indid
                  FROM #dbName.sys.sysindexkeys
                  WHERE id = a.id AND colid=a.colid
                )
              )
            ) THEN 1 ELSE 0 END AS IsPrimaryKey
            ,a.IsNullable AS IsNullable
            ,COLUMNPROPERTY(a.id, a.name, 'IsIdentity') AS IsIdentity
            ,ISNULL(e.text, '') AS DataDefault
            ,CASE
              WHEN g.[value] IS NULL THEN a.Name
              ELSE g.[value]
            END AS Description
          FROM #dbName.sys.syscolumns a
          LEFT JOIN #dbName.sys.systypes b 
            ON a.xusertype=b.xusertype
          INNER JOIN #dbName.sys.objects d 
            ON a.id=d.object_id AND (d.type='U' OR d.type='V') AND d.name<>'dtproperties'
          INNER JOIN #dbName.sys.schemas s
            ON d.schema_id=s.schema_id
          LEFT JOIN #dbName.sys.syscomments e 
            ON a.cdefault=e.id
          LEFT JOIN #dbName.sys.extended_properties g 
            ON a.id=g.major_id AND a.colid=g.minor_id
          WHERE s.name=@schema AND d.name=@table
          ORDER BY a.id, a.colorder;

        SET @index += 1;

        OPEN cur_columns;
        FETCH NEXT FROM cur_columns INTO @name, @dataType, @dataLength, @isPrimaryKey, @isNullable, @isIdentity, @dataDefault, @description;

        SET @columnDescription='';
        SET @sql = 'Create Table ['+@schema+'].['+@table+']('+CHAR(10);

        WHILE @@FETCH_STATUS=0
        BEGIN
          SET @sql+= '  [' + @name + '] '+@dataType;

          IF @dataType in('char', 'nchar', 'varchar', 'nvarchar')
            SET @sql+=' ('+ CASE @dataLength WHEN -1 THEN 'MAX' ELSE CAST(@dataLength AS NVARCHAR(50)) END+')';

          IF @isIdentity=1 SET @sql+=' IDENTITY';

          IF @isPrimaryKey=1 SET @sql+=' Primary Key';
          ELSE
          BEGIN
            IF @isNullable=1 SET @sql+=' NULL';
            ELSE SET @sql+=' NOT NULL';
          END

          SET @sql+=','+CHAR(10);

          IF @description IS NOT NULL
          BEGIN
            SET @columnDescription+='EXEC sys.sp_addextendedproperty ''MS_Description'', N'''+ CONVERT(NVARCHAR(250), @description) + ''' ,''SCHEMA'', '''+@schema+''', ''TABLE'', '''+@table+''', ''COLUMN'', '''+@name+''';'+CHAR(10)+'GO'+CHAR(10);
          END

          FETCH NEXT FROM cur_columns INTO @name, @dataType, @dataLength, @isPrimaryKey, @isNullable, @isIdentity, @dataDefault, @description;
        END
        CLOSE cur_columns;
        DEALLOCATE cur_columns;

        SET @sql=SUBSTRING(@sql, 0, len(@sql)-1)+CHAR(10)+');'+CHAR(10)+'GO'+CHAR(10);
        SET @sql+='EXEC sys.sp_addextendedproperty ''MS_Description'', N'''+ convert(nvarchar(250), @tableDescription) + ''', ''SCHEMA'', '''+@schema+''', ''TABLE'', '''+@table+''', NULL, NULL;'+CHAR(10)+'GO'+CHAR(10);

        INSERT INTO @ddl VALUES(@index,@sql+@columnDescription);

        SET @table=NULL;
        SET @schema=NULL;
        SET @tableDescription=NULL;

        FETCH NEXT FROM cur_tables INTO @schema, @table, @isView, @tableDescription;
      END

      CLOSE cur_tables;
      DEALLOCATE cur_tables;

      SELECT source as [Create Table] FROM @ddl ORDER BY id ASC;
    END`,
  ShowProcessList: `SELECT 
      distinct
      SYS.DM_EXEC_REQUESTS.SESSION_ID AS Id,
      [TEXT] AS Command,
      [name] as db,
      Command as Info,
      CLIENT_NET_ADDRESS AS Host,
      SYS.DM_EXEC_CONNECTIONS.CONNECTION_ID AS '连接ID',
      LAST_READ AS '最后读取时间',
      LAST_WRITE AS '最后写入时间',
      CLIENT_TCP_PORT AS '客户端端口',
      CPU_TIME AS Time,
      case when cpu_time > 10000 then 1 else BLOCKING_SESSION_ID end AS CanKill
    FROM SYS.DM_EXEC_REQUESTS with(nolock)
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) nolock
    LEFT JOIN SYS.DM_TRAN_LOCKS with(nolock) ON SYS.DM_EXEC_REQUESTS.SESSION_ID=SYS.DM_TRAN_LOCKS.REQUEST_SESSION_ID
    LEFT JOIN SYS.DM_EXEC_CONNECTIONS with(nolock) ON SYS.DM_EXEC_CONNECTIONS.SESSION_ID=SYS.DM_EXEC_REQUESTS.SESSION_ID
    left join sys.databases with(nolock) on sys.databases.database_id=sys.dm_exec_requests.database_id
    WHERE RESOURCE_TYPE='OBJECT'
    ORDER BY CPU_TIME DESC`,
  
  Monitor: `use master;EXEC sp_monitor;`
}